package com.atm.dao;

import com.atm.pojo.User;
import com.atm.utill.DBUtils;
import com.atm.utill.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDao {
    private QueryRunner queryRunner=new QueryRunner(DruidUtils.getDataSource());
    /**
     * 查找所有用户
     * @return 所有用户信息
     */
    public List<User> findAllUsers() {
//        Connection connection = DBUtils.getConnection();
//        PreparedStatement preparedStatement = null;
//        ResultSet resultSet = null;
//        try {
//            preparedStatement  = connection.prepareStatement("select * from user");
//            resultSet=preparedStatement.executeQuery();
//            List<User> users = new ArrayList<>();
//            while (resultSet.next()) {
//                User user = new User();
//                user.setIcno(resultSet.getString(1));
//                user.setName(resultSet.getString(2));
//                user.setPwd(resultSet.getString(3));
//                user.setMobile(resultSet.getString(4));
//                user.setIdcard(resultSet.getString(5));
//                user.setBalance(resultSet.getDouble(6));
//                users.add(user);
//            }
//            return users;
//        }catch (Exception e) {
//            e.printStackTrace();
//        }finally {
//            DBUtils.release(connection,preparedStatement,resultSet);
//        }
//        return null;
        try {
            return queryRunner.query("select * from user", new BeanListHandler<>(User.class));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 模糊查询 用户信息
     * @param name 模糊查询名子
     * @return 模糊查询结果
     */
    public List<User> queryUsers(String name) {
//        Connection connection = DBUtils.getConnection();
//        PreparedStatement preparedStatement = null;
//        ResultSet resultSet = null;
//        try {
//            preparedStatement  = connection.prepareStatement("select * from user where name like ?");
//            preparedStatement.setString(1,"%"+name+"%");
//            resultSet=preparedStatement.executeQuery();
//            List<User> users = new ArrayList<>();
//            while (resultSet.next()) {
//                User user = new User();
//                user.setIcno(resultSet.getString(1));
//                user.setName(resultSet.getString(2));
//                user.setPwd(resultSet.getString(3));
//                user.setMobile(resultSet.getString(4));
//                user.setIdcard(resultSet.getString(5));
//                user.setBalance(resultSet.getDouble(6));
//                users.add(user);
//            }
//            return users;
//        }catch (Exception e) {
//            e.printStackTrace();
//        }finally {
//            DBUtils.release(connection,preparedStatement,resultSet);
//        }
//        return null;
        String  sql ="select * from user where name like ?";
        try {
            return queryRunner.query(sql, new BeanListHandler<>(User.class), "%" + name + "%");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 通过icno 查询
     * @param icno 卡号
     * @return 用户信息
     */
    public User getUserByIcno(String icno) {
//        Connection connection = DBUtils.getConnection();
//        PreparedStatement preparedStatement = null;
//        ResultSet resultSet = null;
//        try {
//            preparedStatement  = connection.prepareStatement("select * from user where icno = ?");
//            preparedStatement.setString(1,icno);
//            resultSet=preparedStatement.executeQuery();
//            while (resultSet.next()) {
//                User user = new User();
//                user.setIcno(resultSet.getString(1));
//                user.setName(resultSet.getString(2));
//                user.setPwd(resultSet.getString(3));
//                user.setMobile(resultSet.getString(4));
//                user.setIdcard(resultSet.getString(5));
//                user.setBalance(resultSet.getDouble(6));
//                return user;
//            }
//        }catch (Exception e) {
//            e.printStackTrace();
//        }finally {
//            DBUtils.release(connection,preparedStatement,resultSet);
//        }
//        return null;
        String  sql ="select * from user where icno = ?";
        try {
            return queryRunner.query(sql, new BeanHandler<>(User.class), icno);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 更新用户信息
     * @param user 用户信息
     * @return result
     */
    public int updateUser(User user) {
//        Connection connection = DBUtils.getConnection();
//        PreparedStatement preparedStatement = null;
//        try {
//            preparedStatement  = connection
//                    .prepareStatement("update user set name = ?, pwd = ? ,mobile = ?, idcard = ?, balance = ? where icno = ?");
//            preparedStatement.setString(1,user.getName());
//            preparedStatement.setString(2,user.getPwd());
//            preparedStatement.setString(3,user.getMobile());
//            preparedStatement.setString(4,user.getIdcard());
//            preparedStatement.setDouble(5,user.getBalance());
//            preparedStatement.setString(6,user.getIcno());
//            int num= preparedStatement.executeUpdate();
//            return num;
//        }catch (Exception e) {
//            e.printStackTrace();
//        }finally {
//            DBUtils.release(connection,preparedStatement,null);
//        }
//        return 0;
        String  sql ="update user set name = ?, pwd = ? ,mobile = ?, idcard = ?, balance = ? where icno = ?";
        try {
            return queryRunner.update(sql, user.getName(), user.getPwd(), user.getMobile(), user.getIdcard(), user.getBalance(), user.getIcno());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 根据icno删除用户
     * @param icno 卡号
     * @return reasult
     */
    public int deleteUser(String icno) {
//        Connection connection = DBUtils.getConnection();
//        PreparedStatement preparedStatement = null;
//        try {
//            preparedStatement  = connection
//                    .prepareStatement("delete from user where icno =?");
//            preparedStatement.setString(1,icno);
//            int num= preparedStatement.executeUpdate();
//            return num;
//        }catch (Exception e) {
//            e.printStackTrace();
//        }finally {
//            DBUtils.release(connection,preparedStatement,null);
//        }
//        return 0;
        String sql ="delete from user where icno =?";
        try {
            return queryRunner.update(sql, icno);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 添加用户信息
     * @param user 用户信息
     * @return result
     */
    public int addUser(User user) {
//        Connection connection = DBUtils.getConnection();
//        PreparedStatement preparedStatement = null;
//        try {
//            preparedStatement  = connection
//                    .prepareStatement("insert into  user values(?,?,?,?,?,?)");
//            preparedStatement.setString(1,user.getIcno());
//            preparedStatement.setString(2,user.getName());
//            preparedStatement.setString(3,user.getPwd());
//            preparedStatement.setString(4,user.getMobile());
//            preparedStatement.setString(5,user.getIdcard());
//            preparedStatement.setDouble(6,user.getBalance());
//            int num= preparedStatement.executeUpdate();
//            return num;
//        }catch (Exception e) {
//            e.printStackTrace();
//        }finally {
//            DBUtils.release(connection,preparedStatement,null);
//        }
//        return 0;
        String  sql ="insert into  user values(?,?,?,?,?,?)";
        try {
            return queryRunner.update(sql,
                    user.getIcno(),
                    user.getName(),
                    user.getPwd(),
                    user.getMobile(),
                    user.getIdcard(),
                    user.getBalance());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 根据卡号和密码查询用户信息
     * @param icno 卡号
     * @param pwd 密码
     * @return 用户信息
     */
    public User findUserByNameAndPwd(String icno, String pwd) {
//        Connection connection = DBUtils.getConnection();
//        PreparedStatement preparedStatement = null;
//        try {
//            preparedStatement  = connection
//                    .prepareStatement("select * from user where icno = ? and pwd = ?");
//            preparedStatement.setString(1,icno);
//            preparedStatement.setString(2,pwd);
//            ResultSet resultSet = preparedStatement.executeQuery();
//            User user = new User();
//            if (resultSet.next()) {
//                user.setIcno(resultSet.getString(1));
//                user.setName(resultSet.getString(2));
//                user.setPwd(resultSet.getString(3));
//                user.setMobile(resultSet.getString(4));
//                user.setIdcard(resultSet.getString(5));
//                user.setBalance(resultSet.getDouble(6));
//            }
//            return user;
//        }catch (Exception e) {
//            e.printStackTrace();
//        }finally {
//            DBUtils.release(connection,preparedStatement,null);
//        }
//        return null;
        String sql ="select * from user where icno = ? and pwd = ?";
        try {
            return queryRunner.query(sql, new BeanHandler<>(User.class), icno, pwd);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 更新用户信息
     * @param icno 卡号
     * @param newpwd 新密码
     * @return result
     */
    public int  updateUserPwd(String icno,String newpwd) {
        String sql ="update user set pwd = ? where icno = ?";
        try {
            return queryRunner.update(sql,newpwd, icno);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 根据卡号获取资金
     * @param icno 卡号
     * @return 资金
     */
    public double getUserBalance(String icno) {
//        Connection connection = DBUtils.getConnection();
//        PreparedStatement preparedStatement = null;
//        try {
//            preparedStatement  = connection
//                    .prepareStatement("select balance from user where icno=?");
//            preparedStatement.setString(1,icno);
//            ResultSet resultSet = preparedStatement.executeQuery();
//            double balance=0;
//            if (resultSet.next()) {
//                balance=resultSet.getDouble(1);
//            }
//            return balance;
//        }catch (Exception e) {
//            e.printStackTrace();
//        }finally {
//            DBUtils.release(connection,preparedStatement,null);
//        }
//        return 0;
        String sql ="select balance from user where icno=?";
        try {
            Double query = queryRunner.query(sql, new ScalarHandler<>(), icno);
            return  query;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 存钱
     * @param money 金额
     * @param icno 卡号
     * @return result
     */
    public int  saveMoney(int money,String icno) {
//        Connection connection = DBUtils.getConnection();
//        PreparedStatement preparedStatement = null;
//        try {
//            preparedStatement  = connection
//                    .prepareStatement("update user set balance = balance + ? where icno = ?");
//            preparedStatement.setDouble(1,money);
//            preparedStatement.setString(2,icno);
//            int num = preparedStatement.executeUpdate();
//            return num;
//        }catch (Exception e) {
//            e.printStackTrace();
//        }finally {
//            DBUtils.release(connection,preparedStatement,null);
//        }
//        return 0;
        String sql ="update user set balance = balance + ? where icno = ?";
        try {
           return queryRunner.update(sql,money,icno);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 取钱
     * @param money 金额
     * @param icno 卡号
     * @return result
     */
    public int fetchMoney(int money, String icno) {
//        Connection connection = DBUtils.getConnection();
//        PreparedStatement preparedStatement = null;
//        try {
//            preparedStatement  = connection
//                    .prepareStatement("update user set balance = balance - ? where icno = ?");
//            preparedStatement.setDouble(1,money);
//            preparedStatement.setString(2,icno);
//            int num = preparedStatement.executeUpdate();
//            return num;
//        }catch (Exception e) {
//            e.printStackTrace();
//        }finally {
//            DBUtils.release(connection,preparedStatement,null);
//        }
//        return 0;
        String sql ="update user set balance = balance - ? where icno = ?";
        try {
           return queryRunner.update(sql,  money, icno);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    /**
     *获取卡号对应的邮件
     */
    public String getEmailByIcno(String icno){
        String sql ="select email from user_email where icno = ?";
        try {
          return  queryRunner.query(sql, new ScalarHandler<>(), icno);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

}
